﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using sql表结构对比.Model;
using sql表结构对比.IRepository;
using System.Data.SqlClient;
using sql表结构对比.Repository;
using System.Xml;
using System.IO;
namespace sql表结构对比.DataBusinessLayer
{
    partial class DedicatedService : IDedicatedService
    {
        //下拉框数据绑定
        #region
        //将源数据表中的组织代码绑定到combBox,方便后期按医院来导入数据
        public List<string> BindCombBox(string wx_table)
        {
            SourTestEntities STEntity = new SourTestEntities();
            if (wx_table == "wx_booking")
            {
                return (from u in STEntity.wx_booking select u.OrgId).Distinct().ToList();
            }

            if (wx_table == "wx_dict_dept")
            {
                return (from u in STEntity.wx_dict_dept select u.OrgId).Distinct().ToList();
            }

            if (wx_table == "wx_dict_doctor")
            {
                return (from u in STEntity.wx_dict_doctor select u.OrgId).Distinct().ToList();
            }
            return null;

        }
        #endregion
        //一键导入功能
        public void OneClickImport(List<string> baseinfo, out string erroInfo,string option="")
        {
            string cmd = string.Empty;
            if (option == "") {
               cmd = command(baseinfo);
            }
            else
            {
                cmd += " "+option;
            }
            
            string info = string.Empty;
            SqlHelper.ExecuteNonQuery(cmd, SqlHelper.constrDest, out info);
            erroInfo = info;
            if(erroInfo.Contains("语法错误")){
                erroInfo += "\r\n请检查下面的sql语句\r\n" + cmd;
            }
        }

        //获取指定表的列名
        public List<string> GetColumnNames(string tablename, string constr)
        {
            string cmd = "SELECT COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='" + tablename + "'";
            SqlDataReader reader = SqlHelper.GetReader(cmd, constr);
            List<string> ColumnNames = new List<string>();
            while (reader.Read())
            {
                string Columnname = reader["COLUMN_NAME"].ToString();
                ColumnNames.Add(Columnname);
            }
            return ColumnNames;
        }

        //建立表关系。写入xml文件
        public void AddTableRelationship(List<string> T, List<string> basicinfo)
        {
            XmlDocument xml = new XmlDocument();
            XmlNode root;
            if (File.Exists("tableRelationship.xml"))
            {
                xml.Load("tableRelationship.xml");
            }
            root = xml.SelectSingleNode("Relationship");
            //XmlElement root=  xml.CreateElement("Relationship");
            //xml.AppendChild(root);
            XmlElement tableRelationship = xml.CreateElement("tableRelationship");
            root.AppendChild(tableRelationship);
            XmlElement database = xml.CreateElement("basicInfo");
            tableRelationship.AppendChild(database);
            XmlElement sourDB = xml.CreateElement("sourDB");
            sourDB.InnerText = basicinfo[0];
            database.AppendChild(sourDB);
            XmlElement DesDB = xml.CreateElement("DesDB");
            DesDB.InnerText = basicinfo[1];
            database.AppendChild(DesDB);
            XmlElement SourTb = xml.CreateElement("sourTb");
            SourTb.InnerText = basicinfo[2];
            database.AppendChild(SourTb);
            XmlElement DesTb = xml.CreateElement("DesTb");
            DesTb.InnerText = basicinfo[3];
            database.AppendChild(DesTb);
            XmlElement fieldRelationship = xml.CreateElement("fieldRelationship");
            tableRelationship.AppendChild(fieldRelationship);
            foreach (var item in T)
            {
                XmlElement fieldName = xml.CreateElement("fieldName");
                fieldName.InnerText = item;
                fieldRelationship.AppendChild(fieldName);
            }
            xml.Save(@"tableRelationship.xml");
        }

        //读取xml文件，获取表关系
        public List<tablerelationship> GetTableRelationship()
        {

            List<tablerelationship> list = new List<tablerelationship>();
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load("tableRelationship.xml"); //加载xml文件
            XmlNode xn = xmlDoc.SelectSingleNode("Relationship");
            XmlNodeList xnl = xn.ChildNodes;//所有的tableRelationship
            foreach (XmlNode xnf in xnl)
            {
                XmlElement xe = (XmlElement)xnf;
                XmlNodeList xnf1 = xe.ChildNodes;//database 和 fieldRelationship
                int temp = 0;
                tablerelationship tl = new tablerelationship();
                foreach (XmlNode xn2 in xnf1)
                {
                    //Console.WriteLine(xn2.InnerText);//显示子节点点文本 
                    temp++;
                    XmlElement xnf2_1 = (XmlElement)xn2;
                    if (temp == 1)
                    {
                        foreach (XmlNode xn3 in xnf2_1)
                        {
                            tl.basicinfo.Add(xn3.InnerText);
                        }
                    }
                    if (temp == 2)
                    {
                        foreach (XmlNode xn3 in xnf2_1)
                        {
                            tl.filedRelationship.Add(xn3.InnerText);
                        }
                    }

                }
                list.Add(tl);
            }
            return list;
        }

        //修改表关系，写入xml文件
        public void UpdataTableRelationship(List<string> basicinfo,List<string> relation)
        {
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(@"tableRelationship.xml"); //加载xml文件
            //获取bookshop节点的所有子节点 
            XmlNodeList nodeList = xmlDoc.SelectSingleNode("Relationship").ChildNodes;//获取所有的关系表

            //遍历所有子节点 
            foreach (XmlNode xn in nodeList)
            {
                XmlElement xe = (XmlElement)xn; //将子节点类型转换为XmlElement类型 
                XmlNodeList nls = xe.ChildNodes;//得到一张表里面的子节点（basicinfo节点和fieldRelationship节点）
                int temp = 0;
                bool f = false;
                foreach (XmlNode xn1 in nls)//遍历 
                {
                    temp++;
                    bool flag = false;
                   
                    XmlElement xe2 = (XmlElement)xn1; //转换类型
                    if (temp%2 ==1)
                    {
                        //匹配信息,若不是我要修改的表关系就continue
                        int x = 0;
                     
                        foreach (XmlNode xn3 in xe2)
                        {
                            if (basicinfo[x] == xn3.InnerText) {
                                x++;
                            }
                            else
                            {
                                flag = true;
                                break;
                            }
                            if (x == 4) {
                                f = true;
                                flag= true;
                            }
                        }
                        if (flag == true) {  continue; }
                    }
                 
                    if (f)
                    {
                        xe2.RemoveAll();
                        if (relation.Count == 0) { 
                            //删除一个表关系节点
                            XmlNode xx = xe2.ParentNode;
                            xx.ParentNode.RemoveChild(xx);
                        }
                        else
                        {
                            foreach (var item in relation)
                            {
                                XmlElement fieldName = xmlDoc.CreateElement("fieldName");
                                fieldName.InnerText = item;
                                xe2.AppendChild(fieldName);
                            }
                        }
                        goto less;
                    }
                }
            }

       less: xmlDoc.Save(@"tableRelationship.xml");//保存。 
        }

        //建立一键导入sql语句
        #region
        public tablerelationship comfirm(List<string> basicinfo)
        {
            tablerelationship temp = new tablerelationship();
            temp = null;
            foreach (tablerelationship item in GetTableRelationship())
            {
                int i = 0;
                for (i = 0; i < 4; i++)
                {
                    if (basicinfo[i] != item.basicinfo[i])
                    {
                        break;
                    }
                }
                if (i != 4)
                {
                    continue;
                }
                else
                {
                    temp = item;
                    break;
                }


            }
            return temp;
        }
        public string command(List<string> basicinfo)
        {
            tablerelationship temp = comfirm(basicinfo);
            if (temp == null)
            {
                return "";
            }
            else
            {
                string sourdatabase = temp.basicinfo[0];
                string desdatabase = temp.basicinfo[1];
                string sourTbname = temp.basicinfo[2];
                string desTbname = temp.basicinfo[3];
                string Dfilenames = string.Empty;
                string Sfilenames = string.Empty;
                foreach (string item in temp.filedRelationship)
                {
                    string[] strs = item.Split(new char[2] { '-', '>' });
                    Sfilenames += strs[0] + ",";
                    Dfilenames += strs[2] + ",";
                }
                Sfilenames = Sfilenames.Remove(Sfilenames.LastIndexOf(","), 1);
                Dfilenames = Dfilenames.Remove(Dfilenames.LastIndexOf(","), 1);
                string cmd = "insert into [" + desdatabase.Trim() + "].dbo.".Trim() + desTbname.Trim() + " (" + Dfilenames + ") " + "select " + Sfilenames + " from [" + sourdatabase.Trim() + "].dbo.".Trim() + sourTbname;
                return cmd;
            }
        }
        //*添加控制条件来导入数据（暂时未开放）
        public string contorloOptional(string constr) {
            //编写具体条件,为后期拓展
            return "";
        }
        #endregion


    }
}
